#include "postgresql.h"

#include <QSqlDatabase>
#include <QDebug>
#include <QStringList>
#include <QMessageBox>
#include <QSqlQuery>
#include <QSqlError>
#include <QRegExp>
#include <QDateTime>



extern "C" MY_EXPORT PLUGININFO getPluginInfo(void){
	PLUGININFO tmp;
	tmp.shortName = "PostgreSQL";
	tmp.version = PLUGIN_VERSION(0,0,1,0);
	tmp.versionString = VERSION_TO_STRING( tmp.version );
	tmp.description = QObject::tr("Wtyczka do serwera umożliwiająca "
								  "korzystanie z bazy PostgreSQL");
	tmp.author = "Momus";
	tmp.authorEmail = "klimczak.k@gmail.com";
	tmp.homepage = "http://momus.com.pl";
	return tmp;
}

PostgreSQL::PostgreSQL( QObject *parent ):
		IDatabase(parent),
		m_correctCountTables(3),
		m_defaultPort(5432),
		m_SSL(false),
		m_pSettingsInstance(NULL)
{
	qDebug()<< "Available database drivers:" << QSqlDatabase::drivers();

	m_databaseName = "beaver";
	//m_port = 5432;


	connect(this, SIGNAL(pluginInitiated()), this, SLOT(postInit()));



}

PostgreSQL::~PostgreSQL(){
	if( m_pCore && m_pCore->unregisterSettingPage( m_pSettingsInstance ) ){
		delete m_pSettingsInstance;
	}
	qDebug() << Q_FUNC_INFO;
}

void PostgreSQL::postInit(){
	if( m_pCore ){
		Core::setInstance( m_pCore );
		m_pSettingsInstance = new PostgreSD();
		m_pCore->registerSettingPage( m_pSettingsInstance );
	}
}

bool PostgreSQL::connectToDB(){

	QSqlDatabase db = QSqlDatabase::addDatabase("QPSQL",
												"PostgreSQL::Beaver::Plugin");

	QVariant setting = Core::instance()->settings()->value(
			"plugin/PostgreSQL/hostname", QVariant() );
	if( !setting.isValid() ){
		if( m_hostName.isEmpty() ){
			qWarning() << "PostgreSQL => hostName is empty, using 'localhost'";
			db.setHostName( "localhost" );
		}else
			db.setHostName( m_hostName );
	}else{
		db.setHostName( setting.toString() );
	}
	m_hostName = db.hostName();


	setting = Core::instance()->settings()->value(
			"plugin/PostgreSQL/database", QVariant() );
	if( !setting.isValid() ){
		if( m_databaseName.isEmpty() ){
			qWarning() << "PostgreSQL => db name is empty, using 'beaver'";
			db.setDatabaseName( "beaver" );
		}else
			db.setDatabaseName( m_databaseName );
	}else{
		db.setDatabaseName( setting.toString() );
	}
	m_databaseName = db.databaseName();


	setting = Core::instance()->settings()->value(
			"plugin/PostgreSQL/username", QVariant() );
	if( !setting.isValid() ){
		if( m_userName.isEmpty() ){
			qWarning() << "PostgreSQL => username is empty, using 'postgres'";
			db.setUserName( "postgres" );
		}else
			db.setUserName( m_userName );
	}else{
		db.setUserName( setting.toString() );
	}
	m_userName = db.userName();


	setting = Core::instance()->settings()->value(
			"plugin/PostgreSQL/password", QVariant() );
	if( !setting.isValid() ){
		if( m_password.isEmpty() ){
			qWarning() << "PostgreSQL => password is empty, using empty";
			//db.setPassword( ::empty:: );
		}else
			db.setPassword( m_password );
	}else{
		db.setPassword( setting.toString() );
	}
	m_password = db.password();


	setting = Core::instance()->settings()->value(
			"plugin/PostgreSQL/port", QVariant() );
	if( !setting.isValid() ){
		if( 0 > m_port || 65535 < m_port ){
			qWarning() << "PostgreSQL => invalid port, using default";
			db.setPort( m_defaultPort );
		}else
			db.setPort( m_port );
	}else{
		db.setPort( setting.toInt() );
	}
	m_port = db.port();


	setting = Core::instance()->settings()->value(
			"plugin/PostgreSQL/ssl", QVariant() );
	if( !setting.isValid() ){
		if( m_SSL )
			db.setConnectOptions("requiressl=1");
		else
			db.setConnectOptions();
	}else{
		if( setting.toBool() )
			db.setConnectOptions("requiressl=1");
		else
			db.setConnectOptions();
		m_SSL = setting.toBool();
	}



	const QString re("FATAL:  database \"" +
					 m_databaseName + "\" does not exist");
	const QString re2("server does not support SSL, but SSL was required");

OPEN_CONN:
	if( !db.open() ){

		QString textError = db.lastError().databaseText().trimmed();

		if( re2 == textError ){
			qWarning("Could not connetc to DB using SSL, trying without it");
			db.setConnectOptions();
			m_SSL = false;
			goto OPEN_CONN;
		}

		if( re == textError ){
			db.setDatabaseName(QString());
			if( !db.open() ){
				QMessageBox::critical(0, tr("Connection error"),
									  tr("Could not connect to database"));
				qCritical("Could not connect to database");
			}
			initiateDatabase( true );
		}else{
			QMessageBox::critical(0, tr("Connection error"),
								  tr("Could not connect to database"));
			qWarning()<<textError;
			qCritical("Could not connect to database");
		}

	}

	m_prefix = Core::instance()->settings()->value(
			"plugin/PostgreSQL/prefix", QVariant("beaver_") ).toString();
	QStringList tablesList = db.tables().filter( m_prefix );
	int tables = tablesList.count();

	if( 0 == tables ){
		initiateDatabase();
	}else if( m_correctCountTables > tables){
		initiateDatabase();
		//FIXME
		//should be repairDatabase
	}

	//connect(m_pConnector, SIGNAL(dataReaded(QByteArray)), this,
	//		SLOT(parseData(QByteArray)) );

	//m_pConnector->connectToUrl("http://www.rmf.fm/rss/rss.xml");


	return true;
}

bool PostgreSQL::disconnectFromDb(){
	QSqlDatabase db = QSqlDatabase::database( "PostgreSQL::Beaver::Plugin",
											  false );
	if( !db.isValid() )
		return false;

	if( !db.isOpen() )
		return true;

	db.close();

	QSqlDatabase::removeDatabase( "PostgreSQL::Beaver::Plugin" );

	return true;
}

bool PostgreSQL::canConnect() const {
	return QSqlDatabase::drivers().contains("QPSQL");
}

void PostgreSQL::setSSL(bool ssl){
	m_SSL = ssl;
}

bool PostgreSQL::useSSL() const {
	return m_SSL;
}

int PostgreSQL::getDefaultPort() const {
	return m_defaultPort;
}

inline void PostgreSQL::catchError(QString desc, const QSqlQuery &query){
	if ( query.lastError().isValid() )
		qWarning() << desc << query.lastError();
}

bool PostgreSQL::initiateDatabase( bool createDB ){
	QSqlDatabase db = QSqlDatabase::database( "PostgreSQL::Beaver::Plugin",
											  false );
	QSqlQuery query(db);

	if( createDB ){
		query.exec("CREATE DATABASE "+ m_databaseName + " " +
				   "ENCODING = 'UTF8';");

		if(query.lastError().isValid()){
			catchError("Error:", query);
			return false;
		}

		//get default connection
		QSqlDatabase db = QSqlDatabase::database();
		db.close();
		db.setDatabaseName( m_databaseName );

		if( !db.open() )
			qFatal("cannot reopen database");
	}

	query.exec(QString("CREATE TABLE %1users "
			   "(id bigserial CONSTRAINT %1users_key PRIMARY KEY, "
			   "email varchar(255) NOT NULL CONSTRAINT %1email_uniq UNIQUE, "
			   "password bytea NOT NULL);").arg(m_prefix));

	catchError("Query1:", query);

	query.exec(QString("CREATE TABLE %1channels "
			   "(id bigserial CONSTRAINT %1channels_key PRIMARY KEY, "
			   "rsslink varchar(255) NOT NULL CONSTRAINT %1rssurl_uniq UNIQUE, "
			   "title varchar(255) NOT NULL, "
			   "link varchar(255) NOT NULL, "
			   "url varchar(255), "
			   "description text, "
			   "favicon varchar(255));").arg(m_prefix));

	catchError("Query2:", query);

	query.exec(QString("CREATE TABLE %1news "
			   "(id bigserial CONSTRAINT %1news_key PRIMARY KEY, "
			   "channel bigint REFERENCES %1channels(id), "
			   "title text NOT NULL, "
			   "description bytea NOT NULL, "
			   "org_size integer NOT NULL, "
			   "link varchar(255), "
			   "guid varchar(255), "
			   "pubDate timestamptz NOT NULL);").arg(m_prefix));

	catchError("Query3:", query);


	query.exec(QString("CREATE TABLE %1usernews "
			   "(id bigserial CONSTRAINT %1usernews_key PRIMARY KEY, "
			   "userid bigint REFERENCES %1users(id), "
			   "newsid bigint  REFERENCES %1news(id), "
			   "readed boolean NOT NULL DEFAULT '0', "
			   "deleted boolean NOT NULL DEFAULT '0', "
			   "lastOperation timestamptz NOT NULL DEFAULT now());"
			   ).arg(m_prefix));

	catchError("Query4:", query);

	query.exec(QString("CREATE TABLE %1userchannels "
			   "(id bigserial CONSTRAINT %1userchannels_key PRIMARY KEY, "
			   "user bigint REFERENCES %1users(id), "
			   "channel bigint REFERENCES %1channels(id), "
			   "deleted boolean NOT NULL DEFAULT '0');").arg(m_prefix));

	catchError("Query5:", query);


	query.exec(QString("CREATE INDEX %1userchannels_userI "
					   "ON %1userchannels (userid);").arg(m_prefix));

	catchError("Query6:", query);

	query.exec(QString("CREATE INDEX %11usernews_userI "
					   "ON %11usernews (userid);").arg(m_prefix));

	catchError("Query7:", query);

//	query.exec(QString("INSERT INTO %1channels (title, link, url, description, favicon)"
//			   "VALUES ('RMF24.pl', 'http://www.rmf24.pl/', "
//			   "'http://www.rmf24.pl/s/classic/rmf24.pl.gif', "
//			   "'Radio numer 1 w Polsce! Największa i najpopularniejsza stacja "
//			   "muzyczno-informacyjna. Zawsze najlepsza muzyka i najświeższe "
//			   "fakty. Posłuchaj nas online.',  NULL);").arg(m_prefix));
	return true;
}


bool PostgreSQL::repairDatabase(){
	return false;
}

void PostgreSQL::insertItem(const item &newItem){
	QSqlDatabase db = QSqlDatabase::database( "PostgreSQL::Beaver::Plugin",
											  false );
	QSqlQuery query(db);
	//FIXME
	//kanał jest wpisany na sztywno
//	query.prepare(QString("INSERT INTO %1news (channel, title, description, "
//						  "link, guid, pubDate) "
//						  "VALUES (1,?,?,?,?, to_timestamp(?));")
//				  .arg(m_prefix) );

//	query.addBindValue(newItem.title);
//	query.addBindValue(newItem.description);
//	query.addBindValue(newItem.link);
//	query.addBindValue(newItem.guid);
//	query.addBindValue(
//			QDateTime::fromString(newItem.pubDate, Qt::ISODate).toTime_t());

//	query.exec();

	qDebug()<<"insert:"<<query.lastError();
}

void PostgreSQL::recreate(){
	QSqlDatabase db = QSqlDatabase::database( "PostgreSQL::Beaver::Plugin",
											  false );
	QSqlQuery query(db);
	QSqlQuery query2(db);
/*	query.exec(QString("CREATE TABLE %1news "
			   "(id bigserial CONSTRAINT news_key PRIMARY KEY, "
			   "channel bigint REFERENCES %1channels(id), "
			   "title text NOT NULL, "
			   "description bytea NOT NULL, "
			   "org_size integer NOT NULL, "
			   "link varchar(255), "
			   "guid varchar(255), "
			   "pubDate timestamptz NOT NULL);").arg(m_prefix));
			   */

/*
	query.prepare( QString("SELECT channel, title, description, org_size,"
		"link, guid, date_part('epoch',pubDate)::int FROM %1news2 WHERE id=49;")
					   .arg(m_prefix) );
		if( query.exec() ){
			while(  query.next() ){
				qDebug() << query.value(0).toInt();
				qDebug() << query.value(1).toString();

				quint32 org_size = qToBigEndian(query.value(3).toUInt());
				QByteArray data = QByteArray::fromBase64(query.value(2).toByteArray());
				data.prepend((const char*)&org_size, sizeof(quint32));
				data = qUncompress(data);
				qDebug() << "-------------------";
				qDebug() << data;
				qDebug() << "-------------------";
				qDebug() << query.value(3).toInt();
				qDebug() << query.value(4).toString();
				qDebug() << query.value(5).toString();
				qDebug() << query.value(6).toInt();
			}
		}else{
			qDebug() << query.lastError().databaseText();
		}

*/
/*	query.prepare( QString("SELECT channel, title, description, org_size, "
						   "link, guid, date_part('epoch',pubDate)::int FROM %1news2;")
				   .arg(m_prefix) );
	if( query.exec() ){
		while(  query.next() ){
			query2.prepare(QString("INSERT INTO %1news (channel, title,"
								   " description, org_size, "
								  "link, guid, pubDate) "
								  "VALUES (?,?,E?,?,?,?,to_timestamp(?));")
						  .arg(m_prefix) );

			query2.addBindValue( query.value(0).toInt() );
			query2.addBindValue( query.value(1).toString());
			query2.addBindValue( query.value(2).toByteArray());
			query2.addBindValue( query.value(3).toUInt());*/

			/*QByteArray data = query.value(2).toString().toUtf8();
			QByteArray data2 = qCompress(data, 9);
			quint32 num = 0;
			//tej metody nie ma pod VS, M$ jej zabronił i każe używać memcpy_s
			//której nie ma pod linuksem
			memcpy(&num, data2, sizeof(quint32));
			quint32 org_size = qFromBigEndian(num);

			qDebug() << data2.size() << org_size << org_size - data2.size();
			data2.remove(0,4);

			query2.addBindValue(QVariant(data2.toBase64()));
			query2.addBindValue(org_size);*/
/*
			query2.addBindValue( query.value(4).toString() );
			query2.addBindValue( query.value(5).toString() );
			query2.addBindValue( query.value(6).toInt() );

			if( query2.exec() )
				qDebug() << "OK";
			else
				qDebug() << query2.lastError().databaseText();
		}
	}*/
}

quint64 PostgreSQL::checkLogin(const QString &login, const QString &password){
	QSqlDatabase db = QSqlDatabase::database( "PostgreSQL::Beaver::Plugin",
											  false );
	QSqlQuery query(db);
	query.prepare( QString("SELECT id FROM %1users "
						   "WHERE email=? AND password=E?;").arg(m_prefix)) ;
	query.addBindValue( login );
	query.addBindValue( QByteArray::fromHex(password.toLatin1()).toBase64() );

	if( query.exec() ){
		if( query.next() )
			return query.value(0).toUInt();
	}

	return 0;
}

bool PostgreSQL::addLogin(const QString &login, const QString &password){
	QSqlDatabase db = QSqlDatabase::database( "PostgreSQL::Beaver::Plugin",
											  false );
	QSqlQuery query(db);
	query.prepare( QString("INSERT INTO %1users (email, password) "
						   "VALUES(?, E?);").arg(m_prefix)) ;
	query.addBindValue( login );
	query.addBindValue( QByteArray::fromHex(password.toLatin1()).toBase64() );

	return query.exec();
}

bool PostgreSQL::changePassword(const quint64 &id, const QString &oldPassword,
								const QString &newPassword)
{
	QSqlDatabase db = QSqlDatabase::database( "PostgreSQL::Beaver::Plugin",
											  false );
	QSqlQuery query(db);
	query.prepare( QString("UPDATE %1users SET password=E? "
						   "WHERE id=? AND password=E?;").arg(m_prefix)) ;

	query.addBindValue(
			QByteArray::fromHex( newPassword.toLatin1() ).toBase64() );
	query.addBindValue( id );
	query.addBindValue(
			QByteArray::fromHex( oldPassword.toLatin1() ).toBase64() );

	if( query.exec() && query.numRowsAffected() == 1 )
		return true;

	return false;
}

QList<ChannelEntry> PostgreSQL::getChannels(const quint64 &id){
	QList<ChannelEntry> tmpList;

	QSqlDatabase db = QSqlDatabase::database( "PostgreSQL::Beaver::Plugin",
											  false );
	QSqlQuery query(db);

	query.prepare( QString("SELECT ch.id, ch.title, ch.link, ch.description "
						   "FROM %1userchannels uch, %1channels ch "
						   "WHERE uch.userid=? AND uch.channelid=ch.id;")
				   .arg(m_prefix) );

	query.addBindValue( id );
	if( query.exec() ){
		while( query.next() ){
			ChannelEntry entry;

			entry.id = query.value(0).toUInt();
			entry.title = query.value(1).toString();
			entry.link = query.value(2).toString();
			entry.desc = query.value(3).toString();

			tmpList.push_back( entry );
		}
	}

	return tmpList;
}

QList<NewsEntry> PostgreSQL::getNews(const quint64 &id,
									 const quint64 &channel,
									 const quint32 &lastSync)
{
	QList<NewsEntry> tmpList;

	QSqlDatabase db = QSqlDatabase::database( "PostgreSQL::Beaver::Plugin",
											  false );
	QSqlQuery query(db);

	query.prepare( QString("SELECT un.id, n.channel, n.title, n.org_size, "
						   "n.description, n.link, "
						   "date_part('epoch',n.pubdate)::int, "
						   "date_part('epoch',un.lastoperation)::int, "
						   "un.readed, un.deleted "
						   "FROM %1usernews un, %1news n "
						   "WHERE un.userid=? AND un.newsid=n.id "
						   "AND n.channel=? AND "
						   "un.lastoperation > to_timestamp(?);")
				   .arg(m_prefix) );

	if( query.exec() ){
		while( query.next() ){
			NewsEntry entry;

			entry.id = query.value(0).toUInt();
			entry.channel = query.value(1).toUInt();
			entry.title = query.value(2).toString();
			entry.org_size = query.value(3).toUInt();
			entry.desc = QByteArray::fromBase64(query.value(4).toByteArray());
			entry.link = query.value(5).toString();
			entry.pubdate = query.value(6).toUInt();
			entry.lastOperation = query.value(7).toUInt();
			entry.readed = query.value(8).toBool();
			entry.deleted = query.value(9).toBool();


			tmpList.push_back( entry );
		}
	}

	return tmpList;
}

bool PostgreSQL::addChannel(const quint64 &id, const ChannelEntry &entry){
	QSqlDatabase db = QSqlDatabase::database( "PostgreSQL::Beaver::Plugin",
											  false );
	QSqlQuery query(db);

	query.prepare( QString("INSERT INTO %channels (title, link, url, "
						   "description, favicon) VALUES(?,?,NULL,?,NULL);")
				   .arg(m_prefix) );

	query.addBindValue( entry.title );
	query.addBindValue( entry.link );
	query.addBindValue( entry.desc );

	if( query.exec() && query.numRowsAffected() == 1 )
		return true;

	return false;
}

bool PostgreSQL::subscribeChannel(const quint64 &id, const quint64 &channel){
	QSqlDatabase db = QSqlDatabase::database( "PostgreSQL::Beaver::Plugin",
											  false );
	QSqlQuery query(db);

	query.prepare( QString("SELECT id FROM %1news WHERE channel=?;")
				   .arg(m_prefix) );
	query.addBindValue( channel );

	QList<quint32> newsList;

	if( query.exec() ){
		while( query.next() ){
			newsList.push_back( query.value(0).toUInt() );
		}
	}

	db.transaction();

	time_t time = QDateTime::currentDateTime().toTime_t();

	foreach( quint32 news, newsList ){
		query.prepare( QString("INSERT INTO %1usernews (userid, newsid, "
							   "lastoperation) VALUES (?,?, to_timestamp(?));")
					   .arg(m_prefix) );
		query.addBindValue( id );
		query.addBindValue( news );
		query.addBindValue( static_cast<int>(time) );

		if( !query.exec() ){
			db.rollback();
			return false;
		}
	}


	return db.commit();
}

bool PostgreSQL::updateNews(const quint64 &id, const quint32 &news,
							const quint32 &timestamp, bool readed,
							bool deleted)
{
	QSqlDatabase db = QSqlDatabase::database( "PostgreSQL::Beaver::Plugin",
											  false );

	QSqlQuery query(db);
	query.prepare( QString("UPDATE %1usernews SET "
						   "lastoperation=to_timestamp(?), "
						   "readed=?, deleted=? WHERE userid=? AND newsid=?"
						   "AND lastoperation<to_timestamp(?);")
				   .arg(m_prefix) );

	query.addBindValue( timestamp );
	query.addBindValue( readed );
	query.addBindValue( deleted );
	query.addBindValue( id );
	query.addBindValue( news );
	query.addBindValue( timestamp );

	if( query.exec() && query.numRowsAffected() == 1 )
		return true;

	return false;
}

QT_BEGIN_NAMESPACE
Q_EXPORT_PLUGIN2(postgresql, PostgreSQL)
QT_END_NAMESPACE
